package com.pauldada.sakurabbs.dao;

import com.pauldada.sakurabbs.dto.TaskUserAccount;
import com.pauldada.sakurabbs.entities.Task;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;

import java.util.List;

//指明 数据实体的类名 和 数据实体中的ID字段的类型 !!
public interface TaskRepository extends CrudRepository<Task,Long> {

    @Override
    List<Task> findAll();

    @Query(nativeQuery = true,value =
            "SELECT s.task_name, s.task_stage , s.task_description , COUNT(ps.task_id) AS userAccountCount " +
            "FROM task AS s LEFT JOIN task_user_account AS ps ON ps.task_id = s.task_id " +
            "GROUP BY s.task_name, s.task_stage, s.task_description ORDER BY 4 desc;")      //编写自定义查询数据库
    List<TaskUserAccount> taskUserAccounts();
}
